<?php
/**
 * 数据库操作类
 * 相对路径: inc/sqls.php
 * 作者: 15058593138@qq.com
 */

class DB {
    private $conn;
    
    public function __construct() {
        $this->conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
        if ($this->conn->connect_error) {
            die('数据库连接失败: ' . $this->conn->connect_error);
        }
        $this->conn->set_charset(DB_CHARSET);
    }
    
    /**
     * 执行SQL查询
     */
    public function query($sql) {
        return $this->conn->query($sql);
    }
    
    /**
     * 获取单行数据
     */
    public function getRow($sql) {
        $result = $this->query($sql);
        if ($result && $result->num_rows > 0) {
            return $result->fetch_assoc();
        }
        return null;
    }
    
    /**
     * 获取多行数据
     */
    public function getAll($sql) {
        $result = $this->query($sql);
        $data = [];
        if ($result && $result->num_rows > 0) {
            while ($row = $result->fetch_assoc()) {
                $data[] = $row;
            }
        }
        return $data;
    }
    
    /**
     * 获取总记录数
     */
    public function getCount($sql) {
        $result = $this->query($sql);
        if ($result && $result->num_rows > 0) {
            $row = $result->fetch_row();
            return intval($row[0]);
        }
        return 0;
    }
    
    /**
     * 插入数据
     */
    public function insert($table, $data) {
        $fields = [];
        $values = [];
        foreach ($data as $key => $val) {
            $fields[] = "`{$key}`";
            $values[] = "'{$this->escape($val)}'";
        }
        $sql = "INSERT INTO `{$table}` (" . implode(',', $fields) . ") VALUES (" . implode(',', $values) . ")";
        return $this->query($sql);
    }
    
    /**
     * 更新数据
     */
    public function update($table, $data, $where) {
        $sets = [];
        foreach ($data as $key => $val) {
            $sets[] = "`{$key}` = '{$this->escape($val)}'";
        }
        $sql = "UPDATE `{$table}` SET " . implode(',', $sets) . " WHERE {$where}";
        return $this->query($sql);
    }
    
    /**
     * 删除数据
     */
    public function delete($table, $where) {
        $sql = "DELETE FROM `{$table}` WHERE {$where}";
        return $this->query($sql);
    }
    
    /**
     * 转义字符串
     */
    public function escape($str) {
        return $this->conn->real_escape_string($str);
    }
    
    /**
     * 获取最后插入ID
     */
    public function getInsertId() {
        return $this->conn->insert_id;
    }
    
    /**
     * 创建表(如果不存在)
     */
    public function createTable($sql) {
        return $this->query($sql);
    }
    
    /**
     * 检查表是否存在
     */
    public function tableExists($table) {
        $sql = "SHOW TABLES LIKE '{$table}'";
        $result = $this->query($sql);
        return $result && $result->num_rows > 0;
    }
    
    /**
     * 自动创建日期表
     */
    public function autoCreateDateTable($date) {
        $table = TB_PREFIX . 'recs_' . $date;
        if (!$this->tableExists($table)) {
            $sql = "CREATE TABLE `{$table}` (
                `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
                `unid` varchar(36) COMMENT '唯一' DEFAULT '-',
                `usid` varchar(36) COMMENT '学号账号' DEFAULT '-',
                `room` varchar(64) COMMENT '教室编号' DEFAULT '-',
                `ijie` varchar(64) COMMENT '预定日期' DEFAULT '-',
                `days` varchar(64) COMMENT '节次信息' DEFAULT '-',
                `iwei` varchar(36) COMMENT '位次信息' DEFAULT '-',
                `hema` varchar(36) COMMENT '核验码' DEFAULT '-',
                `atime` datetime COMMENT '增加时间' DEFAULT CURRENT_TIMESTAMP,
                `stats` int(2) COMMENT '状态操作' DEFAULT '1',
                UNIQUE KEY `unid` (`unid`),
                INDEX `usid` (`usid`),
                PRIMARY KEY (`id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8";
            return $this->createTable($sql);
        }
        return true;
    }
    
    /**
     * 关闭连接
     */
    public function close() {
        $this->conn->close();
    }
}
?>